package service

import (
	"database/sql"
	"time"

	"agentdesign/models"
)

// ListCanvasMessages returns messages for a specific page version ordered chronologically.
func (s *ApiService) ListCanvasMessages(versionID string) ([]models.CanvasMessage, error) {
	messages := []models.CanvasMessage{}
	err := s.db.Select(&messages, `
		SELECT id, version_id, prompt_request_id, author, content, created_at
		FROM canvas_messages
		WHERE version_id = ?
		ORDER BY created_at ASC`, versionID)

	return messages, err
}

// CreateCanvasMessage inserts a new canvas message row.
func (s *ApiService) CreateCanvasMessage(message models.CanvasMessage) (int64, error) {
	if message.CreatedAt.IsZero() {
		message.CreatedAt = time.Now()
	}

	result, err := s.db.Exec(`
		INSERT INTO canvas_messages (version_id, prompt_request_id, author, content, created_at)
		VALUES (?, ?, ?, ?, ?)`,
		message.VersionID,
		message.PromptRequestID,
		message.Author,
		message.Content,
		message.CreatedAt,
	)
	if err != nil {
		return 0, err
	}

	return result.LastInsertId()
}

// ListPageSections returns the sections composing a specific page version.
func (s *ApiService) ListPageSections(versionID string) ([]models.PageSection, error) {
	sections := []models.PageSection{}
	err := s.db.Select(&sections, `
		SELECT id, version_id, section_key, label, description, position, component_json, created_at
		FROM page_sections
		WHERE version_id = ?
		ORDER BY position ASC, section_key ASC`, versionID)

	return sections, err
}

// GetPageSection fetches a single page section by identifier.
func (s *ApiService) GetPageSection(sectionID string) (models.PageSection, error) {
	section := models.PageSection{}
	err := s.db.Get(&section, `
		SELECT id, version_id, section_key, label, description, position, component_json, created_at
		FROM page_sections
		WHERE id = ?`, sectionID)

	return section, err
}

// CreatePageSection inserts a new structured section for a page version.
func (s *ApiService) CreatePageSection(section models.PageSection) (models.PageSection, error) {
	if section.ID == "" {
		if err := s.db.Get(&section.ID, "SELECT UUID()"); err != nil {
			return models.PageSection{}, err
		}
	}

	componentJSON := any(nil)
	if len(section.ComponentJSON) > 0 {
		componentJSON = section.ComponentJSON
	}

	_, err := s.db.Exec(`
		INSERT INTO page_sections (id, version_id, section_key, label, description, position, component_json)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		section.ID,
		section.VersionID,
		section.SectionKey,
		section.Label,
		section.Description,
		section.Position,
		componentJSON,
	)
	if err != nil {
		return models.PageSection{}, err
	}

	return s.GetPageSection(section.ID)
}

// UpdatePageSection applies edits to an existing page section.
func (s *ApiService) UpdatePageSection(section models.PageSection) (models.PageSection, error) {
	componentJSON := any(nil)
	if len(section.ComponentJSON) > 0 {
		componentJSON = section.ComponentJSON
	}

	result, err := s.db.Exec(`
		UPDATE page_sections
		SET version_id = ?, section_key = ?, label = ?, description = ?, position = ?, component_json = ?
		WHERE id = ?`,
		section.VersionID,
		section.SectionKey,
		section.Label,
		section.Description,
		section.Position,
		componentJSON,
		section.ID,
	)
	if err != nil {
		return models.PageSection{}, err
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return models.PageSection{}, err
	}
	if rows == 0 {
		return models.PageSection{}, sql.ErrNoRows
	}

	return s.GetPageSection(section.ID)
}

// DeletePageSection removes a section definition.
func (s *ApiService) DeletePageSection(sectionID string) (int64, error) {
	result, err := s.db.Exec("DELETE FROM page_sections WHERE id = ?", sectionID)
	if err != nil {
		return 0, err
	}

	return result.RowsAffected()
}

// GetLatestCodeSection returns the newest stored code snippet for a version.
func (s *ApiService) GetLatestCodeSection(versionID string) (models.PageSection, error) {
	section := models.PageSection{}
	err := s.db.Get(&section, `
		SELECT id, version_id, section_key, label, description, position, component_json, created_at
		FROM page_sections
		WHERE version_id = ?
			AND JSON_UNQUOTE(JSON_EXTRACT(component_json, '$.type')) = 'code'
		ORDER BY created_at DESC
		LIMIT 1`, versionID)

	return section, err
}
